package com.JUtils.excel;

import com.JUtils.date.DateFormatUtils;
import com.JUtils.date.DateUtils;
import lombok.Data;
import org.apache.poi.POIXMLDocumentPart;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.NumberToTextConverter;
import org.apache.poi.xssf.usermodel.*;
import org.openxmlformats.schemas.drawingml.x2006.spreadsheetDrawing.CTMarker;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.lang.reflect.Modifier;
import java.math.BigDecimal;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.*;

/**
 * 解析Excel，支持2003、2007
 *
 * @Author:chenssy
 * @date:2014年8月3日
 */
@Data
public class ExcelReadHelperV2 {

    /**
     * 解析Excel 支持2003、2007<br>
     * 利用反射技术完成propertis到obj对象的映射，并将相对应的值利用相对应setter方法设置到obj对象中最后add到list集合中<br>
     * properties、obj需要符合如下规则：<br>
     * 1、obj对象必须存在默认构造函数，且属性需存在setter方法<br>
     * 2、properties中的值必须是在obj中存在的属性，且obj中必须存在这些属性的setter方法。<br>
     * 3、properties中值得顺序要与Excel中列相相应，否则值会设置错：<br>
     * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;excel:编号    姓名         年龄       性别<br>
     * properties:id  name  age  sex<br>
     *
     * @param file       待解析的Excel文件
     * @param properties 与Excel相对应的属性
     * @param obj        反射对象的Class
     * @return
     * @throws Exception
     * @autor:chenssy
     * @date:2014年8月9日
     */
    @SuppressWarnings("rawtypes")
    public static List<Object> excelRead(File file, String[] properties, Class obj) throws Exception {
        Workbook book = getWorkbook(file);
        return getExcelContent(book, properties, obj);
    }

    public static List<Sheet> getSheets(File file) throws IOException {
        Workbook book = getWorkbook(file);
        int numberOfSheets = book.getNumberOfSheets();
        List<Sheet> sheets = new ArrayList<>();
        for (int i = 0; i < numberOfSheets; i++) {
            sheets.add(book.getSheetAt(i));
        }

        return sheets;
    }

    private static Workbook getWorkbook(File file) throws IOException {
        Workbook book;
        try {
            book = new HSSFWorkbook(new FileInputStream(file));      //解析2007
        } catch (Exception e) {
            book = new XSSFWorkbook(new FileInputStream(file));     //解析2003
        }
        return book;
    }

    public static List<Map<String, CellInfo>> excelRead2(File file, List<String> columnKeys) throws Exception {
        Workbook book = getWorkbook(file);
        return getExcelContent2(book, columnKeys);
    }

    /**
     * 解析Excel 支持2003、2007<br>
     * 利用反射技术完成propertis到obj对象的映射，并将相对应的值利用相对应setter方法设置到obj对象中最后add到list集合中<br>
     * properties、obj需要符合如下规则：<br>
     * 1、obj对象必须存在默认构造函数，且属性需存在setter方法<br>
     * 2、properties中的值必须是在obj中存在的属性，且obj中必须存在这些属性的setter方法。<br>
     * 3、properties中值得顺序要与Excel中列相相应，否则值会设置错：<br>
     * &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;excel：编号    姓名         年龄       性别<br>
     * properties：id  name  age  sex<br>
     *
     * @param file       待解析的Excel文件的路径
     * @param properties 与Excel相对应的属性
     * @param obj        反射对象的Class
     * @return
     * @throws Exception
     * @autor:chenssy
     * @date:2014年8月9日
     */
    @SuppressWarnings("rawtypes")
    public static List<Object> excelRead(String filePath, String[] properties, Class obj) throws Exception {
        File file = new File(filePath);
        if (!file.exists()) {
            throw new Exception("指定的文件不存在");
        }
        return excelRead(file, properties, obj);
    }

    /**
     * 根据params、object解析Excel，并且构建list集合
     *
     * @param book       WorkBook对象，他代表了待将解析的Excel文件
     * @param properties 需要参考Object的属性
     * @param object     构建的Object对象，每一个row都相当于一个object对象
     * @return
     * @throws Exception
     * @autor:chenssy
     * @date:2014年8月9日
     */
    @SuppressWarnings("rawtypes")
    private static List<Object> getExcelContent(Workbook book, String[] properties,
                                                Class obj) throws Exception {
        List<Object> resultList = new ArrayList<>();        //初始化结果解
        Map<String, Method> methodMap = getObjectSetterMethod(obj);
        Map<String, Field> fieldMap = getObjectField(obj);
        for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
            Sheet sheet = book.getSheetAt(numSheet);
            if (sheet == null) {   //谨防中间空一行
                continue;
            }
            for (int numRow = 0; sheet.getRow(numRow) != null; numRow++) {   //一个row就相当于一个Object
                Row row = sheet.getRow(numRow);
                if (row == null) {
                    continue;
                }
                resultList.add(getObject(row, properties, methodMap, fieldMap, obj));
            }
        }
        return resultList;
    }

    private static List<Map<String, CellInfo>> getExcelContent2(Workbook book, List<String> columnKeys) throws Exception {
        List<Map<String, CellInfo>> resultList = new ArrayList<>();        //初始化结果解
        for (int numSheet = 0; numSheet < book.getNumberOfSheets(); numSheet++) {
            Sheet sheet = book.getSheetAt(numSheet);
            if (sheet == null) {   //谨防中间空一行
                continue;
            }

            for (int numRow = 0; sheet.getRow(numRow) != null; numRow++) {   //一个row就相当于一个Object
                Row row = sheet.getRow(numRow);
                if (row == null) {
                    continue;
                }

                resultList.add(getObject2(numSheet + 1, row, columnKeys));
            }
        }

        return resultList;
    }

    /**
     * 获取row的数据，利用反射机制构建Object对象
     *
     * @param row        row对象
     * @param properties Object参考的属性
     * @param methodMap  object对象的setter方法映射
     * @param fieldMap   object对象的属性映射
     * @return
     * @throws Exception
     * @autor:chenssy
     * @date:2014年8月9日
     */
    @SuppressWarnings("rawtypes")
    private static Object getObject(Row row, String[] properties, Map<String, Method> methodMap, Map<String, Field> fieldMap, Class obj) throws Exception {
        Object object = obj.newInstance();
        for (int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
            Cell cell = row.getCell(numCell);
            if (cell == null) {
                continue;
            }
            String cellValue = getValue(cell);
            String property = null;
            try {
                property = properties[numCell].toLowerCase();
            } catch (Exception e) {
                e.printStackTrace();
            }
            Field field = fieldMap.get(property);    //该property在object对象中对应的属性
            Method method = methodMap.get(property);  //该property在object对象中对应的setter方法
            setObjectPropertyValue(object, field, method, cellValue);
        }
        return object;
    }

    private static Map<String, CellInfo> getObject2(int sheetIndex, Row row, List<String> columnKeys) {
        Map<String, CellInfo> resultMap = new HashMap<>();
        short lastCellNum = row.getLastCellNum() > 20 ? row.getLastCellNum() : 20;
        for (int numCell = 0; numCell < lastCellNum; numCell++) {
            Cell cell = row.getCell(numCell, Row.MissingCellPolicy.CREATE_NULL_AS_BLANK);

            String columnAddress = cell.getAddress().toString();
            String columnKey = columnAddress.substring(0, 1);

            if (columnKeys.contains(columnKey)) {
                String value = getValue(cell);
                CellInfo cellInfo = new CellInfo(sheetIndex, cell.getRowIndex() + 1, cell.getColumnIndex() + 1, value);
                resultMap.put(columnKey, cellInfo);
            }
        }

        return resultMap;
    }

    /**
     * 获取图片和位置 (xlsx)
     *
     * @param sheet
     * @return
     */
    public static Map<String, PictureData> getPictures2(XSSFSheet sheet, int sheetIndex) {
        Map<String, PictureData> map = new HashMap<>(100);
        List<POIXMLDocumentPart> list = sheet.getRelations();
        for (POIXMLDocumentPart part : list) {
            if (part instanceof XSSFDrawing) {
                XSSFDrawing drawing = (XSSFDrawing) part;
                List<XSSFShape> shapes = drawing.getShapes();
                for (XSSFShape shape : shapes) {
                    XSSFPicture picture = (XSSFPicture) shape;
                    XSSFClientAnchor anchor = picture.getPreferredSize();
                    CTMarker marker = anchor.getFrom();

                    String key = sheetIndex + "-" + (marker.getRow() + 1) + "-" + (marker.getCol() + 1);
                    map.put(key, picture.getPictureData());
                }
            }
        }

        return map;
    }

    private static Object getObject2(Row row, String[] properties, Map<String, Method> methodMap, Map<String, Field> fieldMap, Class obj) throws Exception {
        Object object = obj.newInstance();
        for (int numCell = 0; numCell < row.getLastCellNum(); numCell++) {
            Cell cell = row.getCell(numCell);
            if (cell == null) {
                continue;
            }
            String cellValue = getValue(cell);
            String property = null;
            try {
                property = properties[numCell].toLowerCase();
            } catch (Exception e) {
                e.printStackTrace();
            }
            Field field = fieldMap.get(property);    //该property在object对象中对应的属性
            Method method = methodMap.get(property);  //该property在object对象中对应的setter方法
            setObjectPropertyValue(object, field, method, cellValue);
        }
        return object;
    }

    /**
     * 根据指定属性的的setter方法给object对象设置值
     *
     * @param obj    object对象
     * @param field  object对象的属性
     * @param method object对象属性的相对应的方法
     * @param value  需要设置的值
     * @throws Exception
     * @autor:chenssy
     * @date:2014年8月10日
     */
    private static void setObjectPropertyValue(Object obj, Field field,
                                               Method method, String value) throws Exception {
        Object[] oo = new Object[1];

        String type = field.getType().getName();
        if ("java.lang.String".equals(type) || "String".equals(type)) {
            oo[0] = value;
        } else if ("java.lang.Integer".equals(type) || "java.lang.int".equals(type) || "Integer".equals(type) || "int".equals(type)) {
            if (value.length() > 0)
                oo[0] = Integer.valueOf(value);
        } else if ("java.lang.Float".equals(type) || "java.lang.float".equals(type) || "Float".equals(type) || "float".equals(type)) {
            if (value.length() > 0)
                oo[0] = Float.valueOf(value);
        } else if ("java.lang.Double".equals(type) || "java.lang.double".equals(type) || "Double".equals(type) || "double".equals(type)) {
            if (value.length() > 0)
                oo[0] = Double.valueOf(value);
        } else if ("java.math.BigDecimal".equals(type) || "BigDecimal".equals(type)) {
            if (value.length() > 0)
                oo[0] = new BigDecimal(value);
        } else if ("java.util.Date".equals(type) || "Date".equals(type)) {
            if (value.length() > 0) {//当长度为19(yyyy-MM-dd HH24:mm:ss)或者为14(yyyyMMddHH24mmss)时Date格式转换为yyyyMMddHH24mmss
                if (value.length() == 19 || value.length() == 14) {
                    oo[0] = DateUtils.string2Date(value, "yyyyMMddHH24mmss");
                } else {     //其余全部转换为yyyyMMdd格式
                    oo[0] = DateUtils.string2Date(value, "yyyyMMdd");
                }
            }
        } else if ("java.sql.Timestamp".equals(type)) {
            if (value.length() > 0)
                oo[0] = DateFormatUtils.formatDate(value, "yyyyMMddHH24mmss");
        } else if ("java.lang.Boolean".equals(type) || "Boolean".equals(type)) {
            if (value.length() > 0)
                oo[0] = Boolean.valueOf(value);
        } else if ("java.lang.Long".equals(type) || "java.lang.long".equals(type) || "Long".equals(type) || "long".equals(type)) {
            if (value.length() > 0)
                oo[0] = Long.valueOf(value);
        }
        try {
            method.invoke(obj, oo);
        } catch (Exception e) {
            e.printStackTrace();
            throw e;
        }
    }

    @SuppressWarnings("static-access")
    private static String getValue(Cell cell) {
        if (cell.getCellType() == Cell.CELL_TYPE_BOOLEAN) {
            return String.valueOf(cell.getBooleanCellValue());
        } else if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            return NumberToTextConverter.toText(cell.getNumericCellValue());
        } else {
            return String.valueOf(cell.getStringCellValue());
        }
    }

    /**
     * 获取object对象所有属性的Setter方法，并构建map对象，结构为Map<'field','method'>
     *
     * @param object object对象
     * @return
     * @autor:chenssy
     * @date:2014年8月9日
     */
    @SuppressWarnings("rawtypes")
    private static Map<String, Method> getObjectSetterMethod(Class object) {
        Field[] fields = object.getDeclaredFields();       //获取object对象的所有属性
        Method[] methods = object.getDeclaredMethods();    //获取object对象的所有方法
        Map<String, Method> methodMap = new HashMap<String, Method>();
        for (Field field : fields) {
            String attri = field.getName();
            for (Method method : methods) {
                String meth = method.getName();
                //匹配set方法 
                if (meth != null && "set".equals(meth.substring(0, 3)) &&
                        Modifier.isPublic(method.getModifiers()) &&
                        ("set" + Character.toUpperCase(attri.charAt(0)) + attri.substring(1)).equals(meth)) {
                    methodMap.put(attri.toLowerCase(), method);       //将匹配的setter方法加入map对象中
                    break;
                }
            }
        }

        return methodMap;
    }

    /**
     * 获取object对象的所有属性，并构建map对象，对象结果为Map<'field','field'>
     *
     * @param object object对象
     * @return
     * @autor:chenssy
     * @date:2014年8月10日
     */
    @SuppressWarnings("rawtypes")
    private static Map<String, Field> getObjectField(Class object) {
        Field[] fields = object.getDeclaredFields();       //获取object对象的所有属性
        Map<String, Field> fieldMap = new HashMap<String, Field>();
        for (Field field : fields) {
            String attri = field.getName();
            fieldMap.put(attri.toLowerCase(), field);
        }
        return fieldMap;
    }

    @Data
    public static class ExcelVO {
        String A1;
        String A2;
        String A3;
        String A4;
        String A5;
        String A6;
        String A7;
        String A8;
        String A9;
        String A10;
    }

    @Data
    public static class CellInfo {
        int sheetIndex;
        int rowIndex;
        int columnIndex;
        Object cellValue;

        CellInfo(int sheetIndex, int rowIndex, int columnIndex, Object cellValue) {
            this.sheetIndex = sheetIndex;
            this.rowIndex = rowIndex;
            this.columnIndex = columnIndex;
            this.cellValue = cellValue;
        }
    }


    //刷一级分类
    private static void processFirstLabel(List<Object> dataList, int k) {
        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_material_label` (`id`, `name`, `parent_id`, `create_user`, `create_time`, `update_user`, `update_time`, `status`, `order`)");

            sql.append(" values( ").append("'%s'").append(",");  //id
            sql.append("'%s'").append(",");  //name
            sql.append("'0'").append(",");  //parent_id
            sql.append("'0'").append(",");  //create_user
            sql.append("'now()'").append(",");  //create_time
            sql.append("'0'").append(",");//update_user
            sql.append("now()").append(",");//update_time
            sql.append("'0'").append(",");//update_time
            sql.append("'%s'").append(");");//update_time

            String dataSql = String.format(sql.toString(), i + 1, excelVO.getA1(), i + 1);

            System.out.println(dataSql);
        }
    }

    //刷2级分类
    private static void process2Label(List<Object> dataList, int k) {

        StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_material_label` (`id`, `name`, `parent_id`, `create_user`, `create_time`, `update_user`, `update_time`, `status`, `order`)");

        sql.append(" values( ").append("'%s'").append(",");  //id
        sql.append("'%s'").append(",");  //name
        sql.append("'%s'").append(",");  //parent_id
        sql.append("'0'").append(",");  //create_user
        sql.append("'now()'").append(",");  //create_time
        sql.append("'0'").append(",");//update_user
        sql.append("now()").append(",");//update_time
        sql.append("'0'").append(",");//update_time
        sql.append("'%s'").append(");");//order
        int id = 8;
        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            id = id + 1;
            String mysql = sql.toString();
            String dataSql = String.format(mysql, id, excelVO.getA2(), excelVO.getA1(), excelVO.getA3());

            System.out.println(dataSql);
        }
    }

    //刷物料
    private static void processwuliao(List<Object> dataList, int k) {

        StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_material` (`id`, `name`, `label_id`, `create_user`, `create_time`, `update_user`, `update_time`, `status`, `order`)");

        sql.append(" values( ").append("'%s'").append(",");  //id
        sql.append("'%s'").append(",");  //name
        sql.append("'%s'").append(",");  //label_id
        sql.append("'%s'").append(",");  //create_user
        sql.append("'%s'").append(",");  //create_time
        sql.append("'%s'").append(",");  //update_user
        sql.append("'%s'").append(",");  //update_time
        sql.append("'%s'").append(",");  //status
        sql.append("'%s'").append(");");  //order


        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            String mysql = sql.toString();
            String dataSql = String.format(mysql, i + 1, excelVO.getA2(), excelVO.getA1(), 0, "now()", 0, "now()", 0, excelVO.getA3());

            System.out.println(dataSql);
        }
    }

    //刷物料属性
    private static void processwuliaoshuxing(List<Object> dataList, int k) {

        StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_material_level` " +
                "(`id`, `material_id`, `name`, `description`, `price`, `unit`, `order`, `urls`, `create_user`, `create_time`, `update_user`, `update_time`, `status`, `min_period`, `max_period`)");

        sql.append(" values( ").append("'%s'").append(",");  //id
        sql.append("'%s'").append(",");  //material_id
        sql.append("'%s'").append(",");  //name
        sql.append("'%s'").append(",");  //description
        sql.append("'%s'").append(",");  //price
        sql.append("'%s'").append(",");  //unit
        sql.append("'%s'").append(",");  //order
        sql.append("'%s'").append(",");  //urls
        sql.append("'%s'").append(",");  //create_user
        sql.append("%s").append(",");  //create_time
        sql.append("'%s'").append(",");  //update_user
        sql.append("%s").append(",");  //update_time
        sql.append("'%s'").append(",");  //status
        sql.append("'%s'").append(",");  //min_period
        sql.append("'%s'").append(");");  //max_period


        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            String mysql = sql.toString();
            Integer price = Integer.valueOf(excelVO.getA5().replace(".00", "").replace("￥", "").replace(",", "").trim());
            String dataSql = String.format(mysql,
                    i + 1
                    , excelVO.getA1(),
                    excelVO.getA3(),
                    excelVO.getA6(),
                    price * 100,
                    excelVO.getA4(),
                    excelVO.getA2(),
                    excelVO.getA9(),
                    0,
                    "now()",
                    0,
                    "now()",
                    0,
                    getNum(excelVO.getA7()),
                    getNum(excelVO.getA8())
            );

            System.out.println(dataSql);
        }
    }

    /**
     * 处理套餐数据
     */
    private static void processTaoCan(List<Object> dataList, int k) {
        StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_model_package` (`id`, `price`, `name`, `description`, `logo_url`, `create_user`, `create_time`, `update_user`, `update_time`, `status`,`order`)  " +
                "VALUES(");
        sql.append("'%s'").append(",");  //id
        sql.append("'%s'").append(",");  //price
        sql.append("'%s'").append(",");  //name
        sql.append("'%s'").append(",");  //description
        sql.append("'%s'").append(",");  //logo_url
        sql.append("'%s'").append(",");  //create_user
        sql.append("%s").append(",");  //create_time
        sql.append("'%s'").append(",");  //update_user
        sql.append("%s").append(",");  //update_time
        sql.append("'%s'").append(",");  //status
        sql.append("'%s'").append(");");  //order

        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            String mysql = sql.toString();
            String dataSql = String.format(mysql,
                    i + 1,
                    0,
                    excelVO.getA1(),
                    excelVO.getA5(),
                    null,
                    0,
                    "now()",
                    "0",
                    "now()",
                    "0",
                    excelVO.getA4()
            );

            System.out.println(dataSql);
        }

    }

    /**
     * 处理套餐物料
     */
    private static void processTaoCanWuliao(List<Object> dataList, int k) {
        StringBuilder sql = new StringBuilder("INSERT INTO `ali_purchase_model_package_material` (`id`, `material_id`, `select_level_id`, `model_id`, `amount`, `create_user`, `create_time`, `update_user`, `update_time`, `status`) " +
                "VALUES(");
        sql.append("'%s'").append(",");  //id
        sql.append("'%s'").append(",");  //material_id
        sql.append("'%s'").append(",");  //select_level_id
        sql.append("'%s'").append(",");  //model_id
        sql.append("'%s'").append(",");  //amount
        sql.append("'%s'").append(",");  //create_user
        sql.append("%s").append(",");  //create_time
        sql.append("'%s'").append(",");  //update_user
        sql.append("%s").append(",");  //update_time
        sql.append("'%s'").append(");");  //status

        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);

            String mName = excelVO.getA2();
            String lName = excelVO.getA3();
            Map<String, Object> map = jdbcSelect(mName, lName);
            Long materialId = Long.valueOf(map.get("materialId").toString());
            Long levelId = Long.valueOf(map.get("levelId").toString());
            String mysql = sql.toString();
            String dataSql = String.format(mysql,
                    i + 1,
                    materialId.toString(),
                    levelId.toString(),
                    excelVO.getA1(),
                    excelVO.getA4(),
                    0,
                    "now()",
                    0,
                    "now()",
                    0
            );

            System.out.println(dataSql);
        }

    }


    private static Integer getNum(String str) {
        str = str.trim();
        String str2 = "";
        if (str != null && !"".equals(str)) {
            for (int i = 0; i < str.length(); i++) {
                if (str.charAt(i) >= 48 && str.charAt(i) <= 57) {
                    str2 += str.charAt(i);
                }
            }

        }
        return Integer.valueOf(str2);
    }


    private static Map<String, Object> jdbcSelect(String mName, String lname) {

        Map<String, Object> objectMap = new HashMap<>();

        Connection connection = null;
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;

        try {
            String driverClass = "com.mysql.jdbc.Driver";
            String url = "jdbc:mysql://rm-bp1249626843qoo90mo.mysql.rds.aliyuncs.com:3306/test?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&allowMultiQueries=true&serverTimezone=PRC&useSSL=false";
            String user = "liumy";
            String pass = "Lmy7345129@";

            Class.forName(driverClass);
            connection = DriverManager.getConnection(url, user, pass);

            StringBuilder sql = new StringBuilder("select ali_purchase_material.id materialId ,ali_purchase_material_level.id as levelId  from ali_purchase_material ");
            sql.append(" inner join ali_purchase_material_level on ali_purchase_material.id=ali_purchase_material_level.`material_id` where ");
            sql.append(" ali_purchase_material.name=? and ali_purchase_material_level.name=?");
            preparedStatement = connection.prepareStatement(sql.toString());

            preparedStatement.setObject(1, mName);
            preparedStatement.setObject(2, lname);
            resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                Long materialId = resultSet.getLong(1);
                Long levelId = resultSet.getLong(2);

                objectMap.put("materialId", materialId);
                objectMap.put("levelId", levelId);
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if (resultSet != null) {
                    resultSet.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (preparedStatement != null) {
                    preparedStatement.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }

            try {
                if (connection != null) {
                    connection.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }

        return objectMap;

    }

    public static void main(String[] args) throws Exception {
        String[] strings = {"A1", "A2", "A3", "A4", "A5", "A6", "A7", "A8", "A9", "A10"};
        List<Object> dataList = excelRead("/Users/liumingyue/Desktop/导入开票数据-0909.xlsx", strings, ExcelVO.class);
        int k = 0;

        getInvoiceSql(dataList, k);
        //processTaoCanWuliao(dataList, k);

        // System.out.println(jdbcSelect());

    }

    private static void getInvoiceSql(List<Object> dataList, int k) {
        StringBuilder sql = new StringBuilder("INSERT INTO `tz_order_invoice` (`order_id`, `company_name`, `number`, `money`, `time`, `remark`, `create_time`, `update_time`, `status`, `vproject_id`, `invoice_content`, ");
        sql.append("`invoice_title`, `company_number`, `bank_deposit`, `bank_no`, `vature_type`, `register_address`, `company_tel`, `consignee_man`, `consignee_address`, `consignee_tel`, ");
        sql.append("`consignee_code`, `invoice_status`, `logistics_no`, `update_user`, `create_user`, `voucher_remark`, `deal_time`)");
        sql.append(" VALUES ( 0, '%s', '%s', %s, '%s', '系统同步', '%s', '%s', 1, %s, '', '%s', '', '', '', 1, '', '', '', '', '', '', 20, '', 1, 1, '', '%s');");

        for (int i = 0; i < dataList.size(); i++) {
            ExcelVO excelVO = (ExcelVO) dataList.get(i);
            String mysql = sql.toString();
            Calendar calendar = new GregorianCalendar(1900, 0, -1);
            Date d = calendar.getTime();
            String time = excelVO.getA7();
            String dd = DateUtils.addDayToDate(Integer.valueOf(time), new SimpleDateFormat("yyyy-MM-dd").format(d), "yyyy-MM-dd");


            String dataSql = String.format(mysql, excelVO.getA4(),
                    excelVO.getA6() == null ? "" : excelVO.getA6(),
                    (int) (Double.valueOf(excelVO.getA3()) * 100),
                    dd,
                    dd,
                    dd,
                    excelVO.getA1(),
                    excelVO.getA5() == null ? "" : excelVO.getA5(),
                    dd
            );

            System.out.println(dataSql);
        }
    }

}
